package com.tianchi.lexiang.background.util;

import java.io.File;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.util.Log;

import com.tianchi.lexiang.bean.LeXiangConstants;
import com.tianchi.lexiang.bean.Song;
import com.tianchi.lexiang.util.DBOperator;

public class InternetAddSongUtil {
	
	private static final String TAG = "InternetAddSongUtil";
	
	private static SQLiteDatabase db = null;
	private static InternetAddSongUtil instance = null;
	
	private static final String DB_PATH = "/system/";
	private static final String DB_NAME = "SOFMIT_DBBSM_NEW.db";
	
	private int pageSize = 11;
	
	private InternetAddSongUtil(){
		
	}
	
	public static InternetAddSongUtil getInstance(){
		if(instance== null){
			instance = new InternetAddSongUtil();
		}
		return instance;
	}
	
	private void createDBConnection(){
		try {
			db = SQLiteDatabase.openDatabase(DB_PATH + DB_NAME, null, SQLiteDatabase.OPEN_READWRITE);
			Log.d(TAG, "db==>" + db);
		} catch (SQLiteException e) {
			Log.e(TAG, e.getMessage());
		}
	}
	
	public void closeDBConnection(){
		if (db != null) {
			Log.d(TAG, "[closeDBConnection]");
			db.close();
			db = null;
		}
	}
	
	public int getCountBySpell(String firstSpell,String year, String mouth){
		int totalPage = 0;
		Log.d(TAG,"[getCountBySpell]....year===>" + year +";;;;;mouth====>" + mouth);
		if(db == null || !db.isOpen()){
			createDBConnection();
		}
		StringBuilder builder = new StringBuilder();
		builder.append("select count(*) from T_BDSM_SONGS where 1=1");
		if (firstSpell != null && !"".equals(firstSpell)) {
			builder.append(" and SONGS_FIRST_SPELL LIKE '" + firstSpell + "%'");
		}
		if(year != null && !"".equals(year)){
			builder.append(" and strftime('%Y',SONGS_DATE) = '" + year + "'");
		}
		if(mouth != null && !"".equals(mouth)){
			builder.append(" and strftime('%m',SONGS_DATE) = '" + mouth + "'");
		}
		Cursor mCursor = db.rawQuery(builder.toString(), null);
		mCursor.moveToFirst();
		totalPage = DBOperator.getInstance().getPages(mCursor.getInt(0),pageSize);
		mCursor.close();
		return totalPage;
	}
	
	public Cursor getSongsByPageAndSpell(int page, String firstSpell, String year, String mouth){
		Log.d(TAG,"[getCountBySpell]....year===>" + year +";;;;;mouth====>" + mouth);
		Cursor mCursor = null;
		StringBuilder builder = new StringBuilder();
		builder.append("select SONGS_ID,SONGS_NAME,SINGER from T_BDSM_SONGS where 1=1");
		if (firstSpell != null && !"".equals(firstSpell)) {
			builder.append(" and SONGS_FIRST_SPELL LIKE '" + firstSpell + "%'");
		}
		if(year != null && !"".equals(year)){
			builder.append(" and strftime('%Y',SONGS_DATE) = '" + year +"'");
		}
		if(mouth != null && !"".equals(mouth)){
			builder.append(" and strftime('%m',SONGS_DATE) = '" + mouth +"'");
		}
		builder.append(" limit " + pageSize + " offset " + DBOperator.getOffsetCount(page, pageSize));
		Log.d(TAG, "[getSongsByPageAndSpell]...sql==>" + builder.toString());
		if(db == null || !db.isOpen()){
			createDBConnection();
		}
		mCursor = db.rawQuery(builder.toString(), null);
		return mCursor;
	}
	
	public void removeDownLoadDataBase(){
	     File file = new File(LeXiangConstants.DOWN_LOAD_PATH);
	     Log.d(TAG, "[removeDownLoadDataBase].......file.exists()===>" + file.exists());
	     if(file.exists()){
	    	 boolean bool = file.delete();
	         Log.d(TAG, "[removeDownLoadDataBase].......bool===>" + bool);
	     }
	}
	
	public Song getDownloadSongInfo(int downloadSongId){
		Song mSong = new Song();
		if(db == null || !db.isOpen()){
			createDBConnection();
		}
		String sql = "select * from T_BDSM_SONGS where SONGS_ID = "+ downloadSongId;
		Cursor mCursor = db.rawQuery(sql, null);
		while(mCursor.moveToNext()){
			mSong.number = mCursor.getInt(Song.SONGS_NUM_INDEX);
			mSong.songName = mCursor.getString(Song.SONGS_NAME_INDEX);
			mSong.singerName = mCursor.getString(Song.SINGER_INDEX);
			mSong.language = mCursor.getString(Song.SONGS_LANGUAGE_INDEX);
			mSong.type = mCursor.getString(Song.SONGS_TYPE_INDEX);
			mSong.mtvOrVcd = mCursor.getString(Song.SONGS_MTVORVCD_INDEX);
			mSong.firstSpell = mCursor.getString(Song.SONGS_FIRST_SPELL_INDEX);
			mSong.nameLength= mCursor.getInt(Song.SONGS_NAME_LENGTH_INDEX);
			mSong.leftArea = mCursor.getInt(Song.SONGS_LEFT_AREA_INDEX);
			mSong.rightArea = mCursor.getInt(Song.SONGS_RIGHT_AREA_INDEX);
			mSong.lyrics = mCursor.getString(Song.SONGS_LYRICS_INDEX);
			mSong.lyricsFirstSpell = mCursor.getString(Song.SONGS_LYRICS_FIRST_SPELL_INDEX);
			mSong.temp = mCursor.getInt(Song.SONGS_TEMP_INDEX);
			mSong.date = mCursor.getString(Song.SONGS_DATE_INDEX);
			mSong.format = mCursor.getString(Song.SONGS_FOEMAT_INDEX);
		}
		mCursor.close();
		return mSong;
	}
}
	
